package moon.excel.template;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.activation.MimeType;

import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.primitives.UnsignedBytes;

import moon.excel.CellRegion;
import moon.excel.ExcelExportWrapper;
import moon.excel.ExcelWraperException;
import moon.excel.constants.ExcelConstants;
import moon.excel.util.ExcelUtil;
import moon.util.StringUtils;

/***
 * 只支持xlsx文件,向下方向的集合动态行生成<br>
 * 
 * @author Sunset
 */
public class TplExcelWrapper {
	private Workbook tplWb;
	private Workbook exportWb;
	private ExcelExportWrapper excelExportWrapper;
	private Sheet exportSheet;
	private static Map<String, TplExportDescribe> tpldesCache = new HashMap<String, TplExportDescribe>();// 模板缓存<key,单元格描述>
	private TplExportDescribe tpldes;// 模板描述
	private Map<Short, Font> fontCache = new HashMap<Short, Font>();// 字体缓存
	private Map<String, XSSFColor> xssfColorCache = new HashMap<String, XSSFColor>();

	/**
	 * 
	 * @param tpl
	 *            模板xlsx文件地址
	 */
	public TplExcelWrapper(String tpl) {
		this(tpl, 0);
	}

	/**
	 * 模板xlsx文件地址
	 * 
	 * @param tpl
	 *            模板xlsx文件地址
	 * @param sheetIndex
	 *            sheet索引
	 */
	public TplExcelWrapper(String tpl, int sheetIndex) {
		TplExportDescribe tpldes = readTpl(tpl, sheetIndex);
		buildExportTpl(tpldes);
	}

	private void buildExportTpl(TplExportDescribe tpldes) {
		try {
			excelExportWrapper = new ExcelExportWrapper(tpldes.getFileType());
			exportWb = excelExportWrapper.getWorkbook();
			exportSheet = excelExportWrapper.buildSheet(tpldes.getSheetName());
			for (Map.Entry<Integer, Integer> e : tpldes.getTplColumnWidth().entrySet()) {
				exportSheet.setColumnWidth(e.getKey(), e.getValue());
			}
		} catch (ExcelWraperException e) {
			throw e;
		} catch (Exception e) {
			e.printStackTrace();
			throw new ExcelWraperException("读取模板失败");
		}
	}

	private MimeType getMimeType(String type) {
		MimeType minetype = null;
		try {
			if (type.equals("xls")) {
				minetype = ExcelConstants.MIMETYPE_XLS;
			} else if (type.equals("xlsx")) {
				minetype = ExcelConstants.MIMETYPE_XLSX;
			} else {
				minetype = new MimeType(type);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (minetype != null && ExcelConstants.MIMETYPE_XLS.match(minetype)) {
			throw new ExcelWraperException("仅支付xlsx文件");
		}
		if (minetype != null && !ExcelConstants.MIMETYPE_XLSX.match(minetype)) {
			throw new ExcelWraperException("请检查文件类型。");
		}
		return minetype;
	}

	/**
	 * 设置excel值
	 * 
	 * @param key
	 *            模板上的key
	 * @param val
	 *            需要填入的值
	 * @return
	 */
	public TplExcelWrapper setVal(String key, Object val) {
		return setVal(key, val, TplGroupDescribe.DYNAMIC_EXTEND_TYPE_XIA);
	}

	/***
	 * 设置列宽
	 * 
	 * @param columnIndex
	 *            列索引
	 * @param width
	 *            宽度
	 * @return
	 */
	public TplExcelWrapper setColumnWidth(int columnIndex, int width) {
		int deswidth = ((width * 7 + 5) / 7 + 1);
		exportSheet.setColumnWidth(columnIndex, (deswidth > 255 ? 255 : deswidth) * 256);
		return this;
	}

	/***
	 * 设置excel值
	 * 
	 * @param key
	 *            模板上的key
	 * @param val
	 *            需要填入的值
	 * @param dynamicExtendType
	 *            对于动态集合拓展方向
	 * @return
	 * @see moon.excel.template.TplGroupDescribe
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public TplExcelWrapper setVal(String key, Object val, int dynamicExtendType) {
		if (val == null) {
			val = "";
		}
		if (StringUtils.isNotBlank(key)) {
			TplGroupDescribe cellDescribe = tpldes.getCellCache().get(key);
			if (cellDescribe != null) {
				Class<?> c = val.getClass();
				if (Collection.class.isAssignableFrom(c)) {
					List fillVals = new ArrayList((Collection) val);
					if (!fillVals.isEmpty()) {
						cellDescribe.setFillVals(fillVals);
						cellDescribe.setDynamicExtendType(dynamicExtendType);
					}
				} else {
					cellDescribe.getFillVals().set(0, val);
				}
			}
		}
		return this;
	}

	private TplChange obgainChange(TplGroupDescribe cd, int scal, int dynamicExtendType) {
		TplChange change = new TplChange(cd);
		switch (dynamicExtendType) {
		case TplGroupDescribe.DYNAMIC_EXTEND_TYPE_SHANG:
			change.setNewFirstRow(cd.getFirstRow() - (cd.getFirstRow() - cd.getLastRow() + 1) * (scal - 1));
			break;
		case TplGroupDescribe.DYNAMIC_EXTEND_TYPE_XIA:
			change.setNewLastRow(cd.getLastRow() + (cd.getLastRow() - cd.getFirstRow() + 1) * (scal - 1));
			break;
		case TplGroupDescribe.DYNAMIC_EXTEND_TYPE_ZUO:
			change.setNewFirstColumn(cd.getFirstColumn() - (cd.getFirstColumn() - cd.getLastColumn() + 1) * (scal - 1));
			break;
		case TplGroupDescribe.DYNAMIC_EXTEND_TYPE_YOU:
			change.setNewLastColumn(cd.getLastColumn() - (cd.getLastColumn() - cd.getFirstColumn() + 1) * (scal - 1));
			break;
		default:
			change = null;
			break;
		}
		if (change != null && (change.getNewFirstRow() < 0 || change.getNewLastRow() < 0 || change.getNewFirstColumn() < 0 || change.getNewLastColumn() < 0)) {
			throw new ExcelWraperException("没有足够的行容纳[" + cd.getKey() + "]填充数据");
		}
		return change;
	}

	/***
	 * 导出excel
	 * 
	 * @param os
	 *            输出流
	 */
	public void export(OutputStream os) {
		try {
			for (TplGroupDescribe cd : tpldes.getCellCache().values()) {
				if (!cd.isSingle()) {
					TplChange change = obgainChange(cd, cd.getFillVals().size(), cd.getDynamicExtendType());
					cd.setNewRange(change.getNewFirstRow(), change.getNewLastRow(), change.getNewFirstColumn(), change.getNewLastColumn());
					handleChange(change, cd.getDynamicExtendType());
				}
			}
			for (TplGroupDescribe cd : tpldes.getCellCache().values()) {
				if (cd.isSingle()) {
					Cell cell = cd.getCell(cd.getKey()).getCell();
					fill(cell, cd.getFirstRow(), cd.getLastColumn(), cd.getFillVals().get(0), cd.getFirstRow(), cd.getLastRow(), cd.getFirstColumn(), cd.getLastColumn());
				} else {
					int width = cd.getTplLastColumn() - cd.getTplFirstColumn() + 1;
					int heigth = cd.getTplLastRow() - cd.getTplFirstRow() + 1;
					for (int i = 0; i < cd.getFillVals().size(); i++) {
						Object o = cd.getFillVals().get(i);
						for (Map.Entry<String, TplCellWraper> e : cd.getCells().entrySet()) {
							TplCellWraper cellWraper = e.getValue();
							Cell cell = cellWraper.getCell();
							String attr = e.getKey();
							Object val = ExcelUtil.invokeGetMethod(o, attr);
							if (cd.getDynamicExtendType() == TplGroupDescribe.DYNAMIC_EXTEND_TYPE_XIA) {
								int begin = cd.getFirstRow() - cd.getTplFirstRow();
								int scale = i * heigth + begin;
								fill(cell, cell.getRowIndex() + scale, cell.getColumnIndex(), val, cellWraper.getFirstRow() + scale, cellWraper.getLastRow() + scale, cellWraper.getFirstColumn(), cellWraper.getLastColumn());
							} else if (cd.getDynamicExtendType() == TplGroupDescribe.DYNAMIC_EXTEND_TYPE_SHANG) {
								int begin = cd.getLastRow() - cd.getTplLastRow();
								int scale = -i * heigth + begin;
								fill(cell, cell.getRowIndex() + scale, cell.getColumnIndex(), val, cellWraper.getFirstRow() + scale, cellWraper.getLastRow() + scale, cellWraper.getFirstColumn(), cellWraper.getLastColumn());
							} else if (cd.getDynamicExtendType() == TplGroupDescribe.DYNAMIC_EXTEND_TYPE_ZUO) {
								int begin = cd.getLastColumn() - cd.getTplLastColumn();
								int scale = -i * width + begin;
								fill(cell, cell.getRowIndex(), cell.getColumnIndex() + scale, val, cellWraper.getFirstRow(), cellWraper.getLastRow(), cellWraper.getFirstColumn() + scale, cellWraper.getLastColumn() + scale);
							} else if (cd.getDynamicExtendType() == TplGroupDescribe.DYNAMIC_EXTEND_TYPE_YOU) {
								int begin = cd.getFirstColumn() - cd.getTplFirstColumn();
								int scale = i * width + begin;
								fill(cell, cell.getRowIndex(), cell.getColumnIndex() + scale, val, cellWraper.getFirstRow(), cellWraper.getLastRow(), cellWraper.getFirstColumn() + scale, cellWraper.getLastColumn() + scale);
							}
						}
					}
				}
			}
			excelExportWrapper.export(os);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 处理边界改变事件 暂时只处理向下扩展
	 * 
	 * @param dynamicExtendType
	 */
	private void handleChange(TplChange change, int dynamicExtendType) {
		for (TplGroupDescribe cd : tpldes.getCellCache().values()) {
			if (!cd.getKey().equals(change.getSourceKey())) {
				if (TplGroupDescribe.DYNAMIC_EXTEND_TYPE_XIA == dynamicExtendType) {
					TplChange xia = new TplChange(cd);
					if (cd.getFirstRow() > change.getOldLastRow() && !(cd.getFirstColumn() > change.getOldLastColumn() || cd.getLastColumn() < change.getOldFirstColumn())) {
						int scale = (change.getNewLastRow() - change.getOldLastRow());
						xia.setNewFirstRow(cd.getFirstRow() + scale);
						xia.setNewLastRow(cd.getLastRow() + scale);
					} else if (cd.getFirstRow() == change.getOldFirstRow() && cd.getLastRow() == change.getOldLastRow()) {
						if (cd.isSingle()) {
							xia.setNewFirstRow(change.getNewFirstRow());
							xia.setNewFirstColumn(change.getNewFirstColumn());
							xia.setNewLastColumn(change.getNewLastColumn());
							xia.setNewLastRow(change.getNewLastRow());
						}
					}
					if (xia.isValid()) {
						cd.setNewRange(xia.getNewFirstRow(), xia.getNewLastRow(), xia.getNewFirstColumn(), xia.getNewLastColumn());
					}
				}
			}
		}
	}

	private void fill(Cell tplCell, int rowI, int colI, Object val, int mergeFirstRow, int mergeLastRow, int mergeFirstColumn, int mergeLastColumn) {
		Row row = exportSheet.getRow(rowI);
		if (row == null) {
			row = exportSheet.createRow(rowI);
		}
		Cell deCell = row.createCell(colI);
		CellStyle cellStyle = exportWb.createCellStyle();
		copyStyle(cellStyle, tplCell.getCellStyle());
		excelExportWrapper.paintCell(deCell, val, cellStyle, "");
		if (mergeFirstRow != mergeLastRow || mergeFirstColumn != mergeLastColumn) {
			excelExportWrapper.mergeRegion(new CellRegion(mergeFirstRow, mergeLastRow, mergeFirstColumn, mergeLastColumn), exportSheet);
		}
	}

	private void copyStyle(CellStyle deStyle, CellStyle srCellStyle) {
		deStyle.setAlignment(srCellStyle.getAlignment());
		deStyle.setBorderBottom(srCellStyle.getBorderBottom());
		deStyle.setBorderLeft(srCellStyle.getBorderLeft());
		deStyle.setBorderRight(srCellStyle.getBorderRight());
		deStyle.setBorderTop(srCellStyle.getBorderTop());
		deStyle.setBottomBorderColor(srCellStyle.getBottomBorderColor());
		deStyle.setDataFormat(srCellStyle.getDataFormat());
		Color color = srCellStyle.getFillForegroundColorColor();
		Color desColor = obgainColor(color);
		if (desColor != null) {
			if (tpldes.getFileType().equals("xlsx")) {
				((XSSFCellStyle) deStyle).setFillForegroundColor((XSSFColor) desColor);
			} else if (tpldes.getFileType().equals("xls")) {
				deStyle.setFillForegroundColor(((HSSFColor) desColor).getIndex());
			}
			deStyle.setFillPattern(srCellStyle.getFillPattern());
		}
		deStyle.setIndention(srCellStyle.getIndention());
		deStyle.setHidden(srCellStyle.getHidden());
		deStyle.setLeftBorderColor(srCellStyle.getLeftBorderColor());
		deStyle.setLocked(srCellStyle.getLocked());
		deStyle.setRightBorderColor(srCellStyle.getRightBorderColor());
		deStyle.setRotation(srCellStyle.getRotation());
		deStyle.setTopBorderColor(srCellStyle.getTopBorderColor());
		deStyle.setVerticalAlignment(srCellStyle.getVerticalAlignment());
		deStyle.setWrapText(srCellStyle.getWrapText());
		deStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		deStyle.setAlignment(CellStyle.ALIGN_CENTER);
		deStyle.setWrapText(true);
		deStyle.setFont(obgainFont(srCellStyle.getFontIndex()));
	}

	private Color obgainColor(Color color) {
		if (tpldes.getFileType().equals("xlsx")) {
			XSSFColor xColor = obgainXssfColor((XSSFColor) color);
			if (xColor != null) {
				return xColor;
			}
		} else if (tpldes.getFileType().equals("xls")) {
			HSSFColor hssfColor = (HSSFColor) color;
			HSSFPalette palette = ((HSSFWorkbook) exportWb).getCustomPalette();
			short[] b = hssfColor.getTriplet();
			HSSFColor desColor = palette.findSimilarColor(b[0], b[1], b[2]);
			if (desColor != null) {
				return desColor;
			}
		}
		return null;
	}

	private Font obgainFont(short fontIndex) {
		Font desFont = null;
		if (fontCache.containsKey(fontIndex)) {
			desFont = fontCache.get(fontIndex);
		} else {
			Font srcFont = tplWb.getFontAt(fontIndex);
			desFont = exportWb.createFont();
			if (tpldes.getFileType().equals("xlsx")) {
				XSSFColor xColor = obgainXssfColor(((XSSFFont) srcFont).getXSSFColor());
				if (xColor != null) {
					((XSSFFont) desFont).setColor(xColor);
				}
			} else if (tpldes.getFileType().equals("xls")) {
				desFont.setColor(srcFont.getColor());
			}
			desFont.setBoldweight(srcFont.getBoldweight());
			desFont.setCharSet(srcFont.getCharSet());
			desFont.setColor(srcFont.getColor());
			desFont.setFontHeight(srcFont.getFontHeight());
			desFont.setFontHeightInPoints(srcFont.getFontHeightInPoints());
			desFont.setFontName(srcFont.getFontName());
			desFont.setItalic(srcFont.getItalic());
			desFont.setStrikeout(srcFont.getStrikeout());
			desFont.setTypeOffset(srcFont.getTypeOffset());
			desFont.setUnderline(srcFont.getUnderline());
			fontCache.put(fontIndex, desFont);
		}
		return desFont;
	}

	private XSSFColor obgainXssfColor(XSSFColor xssfColor) {
		XSSFColor xColor = null;
		if (xssfColor != null) {
			byte[] b = xssfColor.getRgbWithTint();
			if (b != null) {
				String key = Arrays.toString(b);
				if (xssfColorCache.containsKey(key)) {
					xColor = xssfColorCache.get(key);
				} else {
					xColor = new XSSFColor(new java.awt.Color(UnsignedBytes.toInt(b[0]), UnsignedBytes.toInt(b[1]), UnsignedBytes.toInt(b[2])));
					xssfColorCache.put(key, xColor);
				}
			}
		}
		return xColor;
	}

	private TplExportDescribe readTpl(String tpl, int sheetIndex) {
		if (tpldesCache.containsKey(tpl)) {
			tpldes = tpldesCache.get(tpl);
		} else {
			InputStream input = null;
			try {
				input = new FileInputStream(new File(tpl));
				String type = tpl.substring(tpl.lastIndexOf(".") + 1);
				MimeType minetype = getMimeType(type);
				if (ExcelConstants.MIMETYPE_XLS.equals(minetype)) {
					tplWb = new HSSFWorkbook(input);
				} else if (ExcelConstants.MIMETYPE_XLSX.equals(minetype)) {
					tplWb = new XSSFWorkbook(input);
				}
				Sheet sheet = tplWb.getSheetAt(sheetIndex);
				if (sheet == null) {
					throw new RuntimeException("sheet不存在");
				}
				tpldes = obgainTplExportDescribe(sheet, type);
				tpldesCache.put(tpl, tpldes);
			} catch (ExcelWraperException e) {
				throw e;
			} catch (Exception e) {
				e.printStackTrace();
				throw new ExcelWraperException("读取模板失败");
			} finally {
				if (input != null) {
					try {
						input.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
		}
		return tpldes;
	}

	private TplExportDescribe obgainTplExportDescribe(Sheet sheet, String type) {
		TplExportDescribe tpldes = new TplExportDescribe(sheet.getSheetName(), type);
		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				for (int j = 0; j < row.getLastCellNum(); j++) {
					tpldes.getTplColumnWidth().put(j, sheet.getColumnWidth(j));
					Cell cell = row.getCell(j);
					if (cell != null && StringUtils.isNotBlank(cell.getStringCellValue())) {
						TplCellWraper cellWraper = new TplCellWraper(cell);
						CellRangeAddress ca = getMergedRegion(sheet, cell);
						if (ca != null) {
							cellWraper.setMerge(ca.getFirstRow(), ca.getLastRow(), ca.getFirstColumn(), ca.getLastColumn());
						}
						String cellval = cell.getStringCellValue();
						if (cellval.contains(".")) {
							String key = cellval.substring(0, cellval.indexOf("."));
							if (tpldes.getCellCache().containsKey(key)) {
								TplGroupDescribe cellDescribe = tpldes.getCellCache().get(key);
								cellDescribe.addKey(cellval.substring(cellval.indexOf(".") + 1), cellWraper);
							} else {
								TplGroupDescribe cellDescribe = new TplGroupDescribe(key);
								cellDescribe.addKey(cellval.substring(cellval.indexOf(".") + 1), cellWraper);
								tpldes.getCellCache().put(key, cellDescribe);
							}
						} else {
							TplGroupDescribe cellDescribe = new TplGroupDescribe(cellval, cellWraper);
							cellDescribe.getFillVals().add(cellval);
							tpldes.getCellCache().put(cellval, cellDescribe);
						}
					}
				}
			}
		}
		setGroupRange(tpldes);
		return tpldes;
	}

	private void setGroupRange(TplExportDescribe tpldes) {
		for (TplGroupDescribe cd : tpldes.getCellCache().values()) {
			if (cd.isSingle()) {
				Cell cell = cd.getCell(cd.getKey()).getCell();
				cd.initRange(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
			} else {
				int firstrow = Integer.MAX_VALUE;
				int lastrow = 0;
				int firstcol = Integer.MAX_VALUE;
				int lastcol = 0;
				for (TplCellWraper cellWraper : cd.getAllCell()) {
					Cell cell = cellWraper.getCell();
					if (cell.getRowIndex() < firstrow) {
						firstrow = cell.getRowIndex();
					}
					if (cell.getRowIndex() > lastrow) {
						lastrow = cell.getRowIndex();
					}
					if (cell.getColumnIndex() < firstcol) {
						firstcol = cell.getColumnIndex();
					}
					if (cell.getColumnIndex() > lastcol) {
						lastcol = cell.getColumnIndex();
					}
				}
				cd.initRange(firstrow, lastrow, firstcol, lastcol);
			}
		}
	}

	private CellRangeAddress getMergedRegion(Sheet sheet, Cell cell) {
		int sheetmergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetmergerCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstC = ca.getFirstColumn();
			int lastC = ca.getLastColumn();
			int firstR = ca.getFirstRow();
			int lastR = ca.getLastRow();
			if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
				if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
					return ca;
				}
			}
		}
		return null;
	}
}
